﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class FREWORKData : BaseFunction
    {

        byte[] b;
        string value;
        public FREWORKData()
            : base()
        {

        }
        /*    public DataSet LoadTableStructure(DataSet dsTarget)
            {
                try
                {
                    TableQueue.Enqueue("WAFARBEN");
                    TableQueue.Enqueue("SELECT WAFART as ArtNum,	WAFVAR as ArtVar,	 WAFZUSATZ as ArtColor,WAFBEZ as ColorDescription"
                        + "  FROM INTEX.WAFARBEN WHERE RowNum<1");
                     TableQueue.Enqueue("BSSATZ");
                     TableQueue.Enqueue("SELECT  "
                         + "BSKART"
                         + ",BSFDZ"
                         + ",BSSRFNR"
                         + ",BSFAKTOR"
                        + " FROM INTEX.BSSATZ WHERE RowNum<1");
                    TableQueue.Enqueue("BASTKE");
                    TableQueue.Enqueue("SELECT "
                                  + "BAKART"
                   + ",BAKVAR"
                   + ",BAKFARBE"
                   + ",BAKKART"
                   + ",BAKKVAR"
                   + ",BAKKFARBE"
                   + ",BAKPATT"
                   + ",BAKPOS"
                   + ",BAKANZ"
                   + ",BAKABF"
                   + " FROM INTEX.BASTKE WHERE RowNum<1");
                    TableQueue.Enqueue("BSFARB");
                    TableQueue.Enqueue("SELECT  "
                        + "BSFKART"
                        + ",BSFKVAR"
                        + ",BSFKFARBE "
                        + ",BSFBEZ"
                    + " FROM INTEX.BSFARB WHERE RowNum<1");
                    TableQueue.Enqueue("BASTGA");
                    TableQueue.Enqueue("SELECT "
                    +"BAGFDZ"
                    + ",BAGART"
                    + ",BAGVAR"
                    + ",BAGFARBE"
                    + ",BAGPOS" 
                    + ",BAGGART"
                    + ",BAGGVAR"
                    + ",BAGGFARBE"
                    + ",BAGFDZ"
                    + ",BAGKGNET"
                    + ",BAGKGBRUT"
                    +" FROM INTEX.BASTGA WHERE RowNum<1");
                    TableQueue.Enqueue("WASTGA");
                    TableQueue.Enqueue("SELECT  "
                           + "WAGART"
                    + ",WAGVAR"
                    + ",WAGWCOL"
                    + ",WAGPSA"
                    + ",WAGPOS"
                    + ",WAGGART"
                    + ",WAGGVAR"
                    + ",WAGGFARBE"
                    + ",WAGFDZ"
                    + ",WAGKGNET"
                    + ",WAGKGBRUT"
                    + ",WAGEAFAKTOR"
                    + ",WAGBEM"
                    + " FROM INTEX.WASTGA WHERE RowNum<1"); 
                    TableQueue.Enqueue("GSDAT");
                    TableQueue.Enqueue("SELECT "
                    + " GSART"
                    + " ,GSVAR"
                    + " ,GSMAT"
                    + " ,GSNUMART"
                    + " ,GSGARNNR"
                    + " ,GSFACH"
                    + " ,GSRECHNUM"
                    + " ,GSRECHNR"
                     + " FROM INTEX.GSDAT WHERE RowNum<1"); 
                    TableQueue.Enqueue("GASTGA");
                    TableQueue.Enqueue("SELECT "
                        +"GAGART"
                        +",GAGVAR"
                        +",GAGFARBE"
                        +",GAGPOS"
                        +",GAGGART"
                        +",GAGGVAR"
                        +",GAGGFARBE"
                        +",GAGKGNET"
                        + ",GAGKGBRUT"   
                       + " FROM INTEX.GASTGA Where RowNum<1"); 
                    TableQueue.Enqueue("GSCOLOR");
                    TableQueue.Enqueue("SELECT GSCART,GSCVAR,GSCFARBE,GSCBEZ FROM INTEX.GSCOLOR WHERE RowNum<1");
                    while (TableQueue.Count > 0)
                    {
                        string TableName = TableQueue.Dequeue() as string;
                        string QueryStr = TableQueue.Dequeue() as string;
                        if (!dsTarget.Tables.Contains(TableName))
                        {
                            orlReadCommand = OracleDB.CreateCommand();
                            orlReadCommand.CommandText = QueryStr;
                            setResult = Connect2Proxy(orlReadCommand);
                            if (setResult.Tables.Count > 0)
                            {
                                setResult.Tables[0].TableName = TableName;
                            }
                            foreach (DataTable dtTemp in setResult.Tables)
                            {
                                dsTarget = ImportDataTable(dsTarget, dtTemp);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                     Tracer.Error(ex.StackTrace, ex); 
                }
                return dsTarget;
            }*/
        public FREWORKData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(DataRow dwWBBST)
        {
            string TableName = "WBBST";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.WBBSTKNR,ROUND(DECODE(A.WBBWAKZ,'F',A.WBBISTKLG/1000/0.9144,A.WBBISTKLG/1000),1) AS QTY,"
                    + " A.WBBDPART, A.WBBBEM,A.WBBAUFKRS,A.WBBAUFLFD,A.WBBAUFPOS,WBBWBMLFDNR FROM WBBST A "
                    + " WHERE A.WBBMANDANT='00' AND A.WBBAUFKRS = :WBBAUFKRS AND A.WBBAUFLFD =:WBBAUFLFD AND A.WBBAUFPOS = :WBBAUFPOS"
               ;

                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    
                    string ColumnName ;
                    string ORDERNO="";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "WBBBEM";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        /*ORDERNO = dwTemp["WBFKRWKRS"].ToString()
                            +dwTemp["WBFKRWLFD"].ToString()
                            +dwTemp["WBFKRWPOS"].ToString()
                            +dwTemp["WBFKVERSION"].ToString()
                            ;
                        dwTemp["REASON"] = GetCommentsFromTXTIND("WBP", ORDERNO);*/
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

        public DataSet GetABPOS(DataRow dwWBBST)
        {

            string TableName = "ABPOS";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setUI = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT D.LSNAME1,B.ABPAUFKRS||'-'||TO_NUMBER(B.ABPAUFLFD)||'-'||TO_NUMBER(B.ABPAUFPOS) AS ORDERNO,"
                + " A.ABKKDAUFTNR,A.ABKKDZEICH,C.HRNNAME,B.ABPAUFKRS,B.ABPAUFLFD,B.ABPAUFPOS,B.ABPBESCHICH,'' AS COLORDES,  B.ABPWAKZ, "
                + " TRIM(B.ABPART)||'-'||TRIM(B.ABPFARBE)||'-'||B.ABPVAR AS ARTICLE,B.ABPSOLLMENGE1/100 AS QTY,'' AS REASON "
                + "  ,ROUND(B.ABPZUGET/100/0.9144,1) AS ABPZUGET, B.ABPZUGSTK "
                + " FROM ABKOPF A,ABPOS B,HRSTAMM C,LIEFDAT D "
                + " WHERE A.ABKMANDANT = B.ABPMANDANT AND A.ABKAUFKRS = B.ABPAUFKRS "
                + " AND A.ABKAUFLFD = B.ABPAUFLFD AND A.ABKMANDANT = C.HRMANDANT "
                + " AND A.ABKARTSB = C.HRBENID AND B.ABPMANDANT = D.LSMANDANT "
                + " AND B.ABPLIEFNR = D.LSLIEFNR AND A.ABKAUFART = 'AM' AND B.ABPERLKZ <>'J' "
                + " AND B.ABPAUFKRS = :ABPAUFKRS AND B.ABPAUFLFD = :ABPAUFLFD AND B.ABPAUFPOS = :ABPAUFPOS " ;

                orlReadCommand.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "LSNAME1";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "ABPBESCHICH";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "HRNNAME";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        string order = dwTemp["ABPAUFKRS"].ToString() + dwTemp["ABPAUFLFD"].ToString() + dwTemp["ABPAUFPOS"].ToString();
                        dwTemp["REASON"] = GetCommentsFromTXTIND("ABP", order);
                        if (dwTemp["ABPWAKZ"] == "F")
                        {
                            double QTY = System.Convert.ToDouble(dwTemp["QTY"].ToString()) / 0.9144;
                            dwTemp["QTY"] = System.Math.Round(QTY, 1);

                        }

                    }
                }
                foreach (DataTable dtTemp in setResult.Tables)
                {
                
                setUI = ImportDataTable(setUI, dtTemp);
                DataTable tableABPOS = setUI.Tables["ABPOS"];
                foreach (DataRow dwTmp in tableABPOS.Rows)
                {
                    setUI = GetCOLORDES(setUI, dwTmp);
                }
                                   
                }
                setReturn.Merge(setUI);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet LoadABPOS()
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT D.LSNAME1,B.ABPAUFKRS||'-'||TO_NUMBER(B.ABPAUFLFD)||'-'||TO_NUMBER(B.ABPAUFPOS) AS ORDERNO,"
                + " A.ABKKDAUFTNR,A.ABKKDZEICH,C.HRNNAME,B.ABPAUFKRS,B.ABPAUFLFD,B.ABPAUFPOS,B.ABPBESCHICH, B.ABPWAKZ,A.ABKAEDAT, "
                + " TRIM(B.ABPART)||'-'||TRIM(B.ABPFARBE)||'-'||B.ABPVAR AS ARTICLE,B.ABPSOLLMENGE1/100 AS QTY,'' AS REASON "
                + "  ,ROUND(B.ABPZUGET/100/0.9144,1) AS ABPZUGET, B.ABPZUGSTK "
                + " FROM ABKOPF A,ABPOS B,HRSTAMM C,LIEFDAT D "
                + " WHERE A.ABKMANDANT = B.ABPMANDANT AND A.ABKAUFKRS = B.ABPAUFKRS "
                + " AND A.ABKAUFLFD = B.ABPAUFLFD AND A.ABKMANDANT = C.HRMANDANT "
                + " AND A.ABKARTSB = C.HRBENID AND B.ABPMANDANT = D.LSMANDANT "
                + " AND B.ABPLIEFNR = D.LSLIEFNR AND A.ABKAUFART = 'AM' AND B.ABPERLKZ <>'J' "; 
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "ABPOS";
                    
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                    {

                        ColumnName = "LSNAME1";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "ABPBESCHICH";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "HRNNAME"; 
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        string order = dwTemp["ABPAUFKRS"].ToString() + dwTemp["ABPAUFLFD"].ToString() + dwTemp["ABPAUFPOS"].ToString();
                        dwTemp["REASON"] = GetCommentsFromTXTIND("ABP", order);
                        if (dwTemp["ABPWAKZ"] == "F")
                        {
                            double QTY= System.Convert.ToDouble(dwTemp["QTY"].ToString())/0.9144;
                            dwTemp["QTY"] = System.Math.Round(QTY, 1);
                                
                        }
                                               
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetCOLORDES(DataSet dsTarget, DataRow dwABPOS)
        {
            string TableName = "COLORDES";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDDATEN,2,30) AS COLORDES FROM SDDAT "
                    + " WHERE SUBSTR(SDKEY,1,2)='00' AND trim(SUBSTR(SDKEY,3,10))=:ABPBESCHICH  AND SDSA = '9998' "
                     ;
                orlReadCommand.Parameters.Add("ABPBESCHICH", OracleType.NVarChar).Value = dwABPOS["ABPBESCHICH"].ToString().Trim();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "";
                    foreach (DataRow dw in dsResult.Tables["COLORDES"].Rows)
                    {
                        ColumnName = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;

                        dwABPOS["COLORDES"] = dw["COLORDES"];
                    }
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    if (dsTarget.Tables.Contains(TableName))
                    {
                        dsTarget.Tables[TableName].Clear();
                    }
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }
                dsTarget.AcceptChanges();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public DataSet BuildData(DataRow dwWBBST)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetABPOS(dwWBBST);
                setReturn.Merge(setResult);
               
                setResult = GetWBBST(dwWBBST);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}